from .guidancesql import *


def selprologue(cmpid, serviceid, dbname, cid):
    htypeid = memcachcon.if_exist(str(dbname) + 'sharetype' + str(cid))
    sql = "select h.HoodleMobileadress,h.HoodleMobileid,h.type,h.qtype,h.sceneid,h.remark,(case when h.interrupt=0 then 0 ELSE '' end),(case when h.skill=1 then 5 else '' end),h.voxfile,h.questionnairetype,h.matchidlist  from zt_know_hoodlemobileid h where " \
          "h.sceneid=1 and h.cmpid=" + \
        str(cmpid) + " and serviceid=" + str(serviceid) + " and h.state=1 "

    if htypeid:
        sql += " and h.htypeid = '" + str(htypeid) + "'"
    else:
        sql += " and (h.htypeid is null or h.htypeid=0)"
    sql += " ORDER BY h.seqencing ASC LIMIT 0,1;"
    prologue = conn.mysql_sel(sql, dbname, cid)

    return prologue


def selTAG(cmpid, serviceid, dbname, cid):
    sql = "select h.HoodleMobileadress,h.HoodleMobileid,h.type,h.qtype,h.sceneid,h.remark,(case when h.interrupt=0 then 0 ELSE '' end),h.voxfile from zt_know_hoodlemobileid h where " \
          "h.sceneid=4 and h.cmpid=" + str(cmpid) + " and serviceid=" + str(
              serviceid) + " and h.state=1 ORDER BY h.seqencing ASC LIMIT 0,1;"
    TAG = conn.mysql_sel(sql, dbname, cid)
    print('sql---def selTAG---phonesql', sql)
    print('TAG---def selTAG---phonesql', TAG)
    return TAG


def selknowspeak(cmpid, serviceid, num, dbname, cid):
    sql = ""
    if str(num) == '1':
        sql = "select r.noanswer,r.vtype,r.noanswertext from zt_cmp_robot r where r.cmpid='" + str(
            cmpid) + "' and r.onserviceid='" + str(serviceid) + "' "
    else:
        sql = "select r.noansweragain,r.vtype,r.noansweragaintext from zt_cmp_robot r where r.cmpid='" + str(
            cmpid) + "' and r.onserviceid='" + str(serviceid) + "' "

    TAG = conn.mysql_sel(sql, dbname, cid)
    return TAG


def selask(cmpid, serviceid, cid, dbname):
    sql = "select h.HoodleMobileid,h.HoodleMobileadress from zt_know_hoodlemobileid h where h.cmpid=" + str(cmpid) + " and \
         h.serviceid=" + str(serviceid) + " and h.state=1 and h.HoodleMobileid not in  \
         (SELECT a.HoodleMoboleid from zt_know_ask a where a.cmpid=" + str(cmpid) + " and a.cid='" + str(
        cid) + "') ORDER BY h.seqencing ASC LIMIT 0,1;"

    ask = conn.mysql_sel(sql, dbname, cid)
    return ask


def insertask(cmpid, HoodleMoboleid, cid, senceid, session_data, session_sort, dbname):
    sql = "INSERT into zt_know_ask(cmpid,HoodleMoboleid,state,cid,senceid,session_data,session_sort) VALUES " \
          " (" + str(cmpid) + "," + str(HoodleMoboleid) + ",0,'" + str(cid) + "','" + str(senceid) + "','" + str(
              session_data) + "','" + str(session_sort) + "')"
    insertid = conn.mysql_insertid(sql, dbname, cid)
    # print(sql)
    return insertid


def insertaskbeginandend(cmpid, HoodleMoboleid, cid, senceid, dbname):
    sql = "INSERT into zt_know_ask(cmpid,HoodleMoboleid,state,cid,senceid) VALUES " \
          " (" + str(cmpid) + "," + str(HoodleMoboleid) + \
        ",0,'" + str(cid) + "','" + str(senceid) + "')"
    insertid = conn.mysql_insertid(sql, dbname, cid)
    return insertid


def selanswertype(cmpid, askid, cid, dbname):
    sql = " SELECT h.answertypeid from zt_know_hoodlemobileid h where h.HoodleMobileid" \
          "  in  (select a.HoodleMoboleid from zt_know_ask a where a.askid='" + \
        str(askid) + "');"
    answertypeid = conn.mysql_selrowid(sql, dbname, cid)
    return answertypeid


def updateask(cmpid, cid, askid, answerid, session_data, session_sort, dbname):
    sql = "UPDATE zt_know_ask set state=1,answerid=" + str(answerid) + " where cmpid= " + str(cmpid) + " and " \
                                                                                                       "cid='" + str(
        cid) + "' and HoodleMoboleid=" + str(askid) + "  "
    # if session_data!="" and session_sort!="":
    #     sql += " and session_data='"+str(session_data)+"' and session_sort='"+str(session_sort)+"'"
    sql += " ORDER BY askid DESC LIMIT 1"

    updatecount = conn.mysql_updata(sql, dbname, cid)
    # print(sql)
    return updatecount


def insertanswer(cmpid, answer, dbname, cid):
    sql = "insert into zt_know_answer(answer,cmpid,answertype) VALUES ('" + \
        str(answer) + "','" + str(cmpid) + "',2); "
    answerid = conn.mysql_insertid(sql, dbname, cid)

    return answerid


def insertquestion(cmpid, question, answerid, serviceid, dbname, cid):
    sql = "insert into zt_know_question(question,state,cmpid,sourcetype,answerid,serviceid,createtime)" \
          " VALUES ('" + str(question) + "',1,'" + str(cmpid) + "',3,'" + str(answerid) + "','" + str(
              serviceid) + "',NOW());"

    insertcount = conn.mysql_insert(sql, dbname, cid)
    return insertcount


# 查询是否有静音配置
def mute_attitude(HoodleMobileid, dbname, cid):
    sql = "select count(*) from zt_know_hoodlemobile_answer a where a.attitude=14 and a.HoodleMobileid='" + str(
        HoodleMobileid) + "' "
    count = conn.mysql_selrowid(sql, dbname, cid)
    return count


# 态度的跳转
# type = 1问题跳转 2场景跳转 3转人工 4发短信 5流程继续 6转号码 7交接
def selnextround(attitude, hoodleMobileid, dbname, cid):
    sql = "select a.type,(case when a.type=1 then a.nextHoodleMobileid when a.type=2 then  a.sceneid when " \
          " a.type=3 then  a.serviceid when a.type=4 then a.modelid when a.type=6 then a.mobile end),a.sceneid,a.attrids,a.HoodleMobileAnswerid,'' as theempty,a.itemid from " \
          "zt_know_hoodlemobile_answer a where a.attitude=" + str(attitude) + " and a.HoodleMobileid=" + str(
              hoodleMobileid) + " ;"
    attitudenum = conn.mysql_sel(sql, dbname, cid)
    # print(sql):
    return attitudenum


def selnextround1(attitude, hoodleMobileid, dbname, cid):
    sql_value_attrid = "select l.attrid from zt_sale_data s ,zt_customer_attr_link l where s.saleid='" + str(
        cid) + "' and l.tmcustid = s.temporarycustomerid"
    value_attrid = conn.mysql_selrowid(sql_value_attrid, dbname, cid)
    print('value_attrid', value_attrid)
    sql = "select a.type,(case when a.type=1 then l.nextHoodleMobileid when a.type=2 then  a.sceneid when " \
          " a.type=3 then  a.serviceid when a.type=4 then a.modelid when a.type=6 then a.mobile end),a.sceneid,a.attrids,a.HoodleMobileAnswerid,'' as theempty,a.itemid from " \
          "zt_know_hoodlemobile_answer a ,zt_know_hoodlemobile_answer_link l where a.attitude=" + str(
              attitude) + " and a.HoodleMobileid=" + str(hoodleMobileid) + " and " \
        "l.HoodleMobileAnswerid=a.HoodleMobileAnswerid and l.attitudeids=" + str(
              value_attrid) + " ;"
    attitudenum = conn.mysql_sel(sql, dbname, cid)
    print('sql--------------phonesql', sql)
    print('attitudenum', attitudenum)
    return attitudenum


# 问卷
# 1：多选（且的关系）2：多选（或的关系）3：单选（或的关系）4.无标准答案
def sel_questionnaire(hoodleMobileid, dbname, cid):
    sql = "select a.attitude,a.matchid from zt_know_hoodlemobile_answer a where   a.HoodleMobileid=" + str(
        hoodleMobileid) + "  and (a.matchid!='' or a.matchid is not null)"
    attitude_match = conn.mysql_seldic(sql, dbname, cid)
    # print(sql)
    return attitude_match


def SelectAttribute(attitude, userid, HoodleMobileid, dbname, cid):
    sql = "select khal.type,(case when khal.type=1 then khal.nextHoodleMobileid when khal.type=2 then  khal.sceneid" \
          " when khal.type=3 then  khal.serviceid when khal.type=4 then khal.modelid when khal.type=6 then khal.mobile end),khal.sceneid,khal.attrids,khal.attrids,'' as theempty,khal.itemid " \
          " from zt_know_hoodlemobile_answer zkha,zt_know_hoodlemobile_answer_link khal,zt_guser_interest zgi " \
          "where zkha.attitude=" + str(
              attitude) + " and zgi.attrid = khal.attitudeids and zkha.HoodleMobileAnswerid = khal.HoodleMobileAnswerid and zgi.userid=" + str(
              userid) + "  and zkha.HoodleMobileid= " + str(HoodleMobileid) + "; "

    attitudenum = conn.mysql_sel(sql, dbname, cid)
    return attitudenum


def SelectAttribute1(attitude, dbname, cid):
    sql = "SELECT calltype FROM zt_dict_attr where attrid = " + \
        str(attitude) + ";"
    attitudenum1 = conn.mysql_selrowid(sql, dbname, cid)
    return attitudenum1


# 查询态度分支
def sel_branch_attitude(HoodleMobileAnswerid, dbname, cid):
    sql = "select a.type,(case when a.type=1 then a.nextHoodleMobileid when a.type=2 then  a.sceneid when " \
          " a.type=3 then  a.serviceid when a.type=4 then a.modelid when a.type=6 then a.mobile end),a.sceneid,a.attrids,a.attitudeids,(case when a.htypeid>0 then a.htypeid else '' end),a.itemid  from " \
          "zt_know_hoodlemobile_answer_link a where a.HoodleMobileAnswerid='" + \
        str(HoodleMobileAnswerid) + "' "
    # if htypeid!="":
    #     sql += " and a.htypeid = '"+str(htypeid)+"'"
    # else:
    #     sql += " and a.attrids != ''"
    attitudelist = conn.mysql_sel(sql, dbname, cid)
    # print(sql)
    return attitudelist


# 查询复制机器人的流程分支
def selcopychatterbottype(copycmpid, serviceid, dbname, cid):
    sql = "SELECT (case when s.htypeid is null or s.htypeid=0 then '' else s.htypeid end) from zt_cmp_robot_share s where s.cmpid='" + str(
        copycmpid) + "' and s.serviceid='" + str(serviceid) + "'"
    htypeid = conn.mysql_selrowid(sql, dbname, cid)
    return htypeid


# 查询分支条件是否存在
def sel_conditions(attridlist, cid, dbname):
    sql = "SELECT l.attrid from zt_customer_attr_link l where l.saleid='" + str(cid) + "' and l.attrid in (" + str(
        attridlist) + ") GROUP BY l.session_data,l.attrid"
    attrid = conn.mysql_sel(sql, dbname, cid)
    count = len(attrid)
    return count


# 查询是否是邀约
def selifinvite(HoodleMobileid, dbname, cid):
    sql = "select count(*) from zt_know_hoodlemobileid h where h.HoodleMobileid=" + str(
        HoodleMobileid) + " and h.sceneid=3"
    count = conn.mysql_selrowid(sql, dbname, cid)
    return count


# 更新邀约是否成功
def updatainvite(cid, attitude, dbname):
    sql = "UPDATE zt_sale_data set isagree=" + \
        str(attitude) + " where saleid='" + str(cid) + "'"
    updata = conn.mysql_updata(sql, dbname, cid)
    # print(sql)
    return updata


def selHoodleMobileadress(HoodleMobile, dbname, cid):
    sql = "select h.HoodleMobileadress,h.sceneid,h.type,h.qtype,h.remark,(case when h.interrupt=0 then 0 ELSE '' end),h.voxfile,h.questionnairetype,h.matchidlist from zt_know_hoodlemobileid h where h.HoodleMobileid=" + str(
        HoodleMobile) + " "
    HoodleMobileadress = conn.mysql_sel(sql, dbname, cid)
    print("******************************selHoodleMobileadress**********************")
    print(sql)
    print(dbname, cid)
    # print(sql)
    print("***********************************************************************************")
    return HoodleMobileadress


def selHoodleMobileadressinterrupt(HoodleMobile, dbname, cid):
    sql = "select  h.HoodleMobileadress ,h.HoodleMobileid,h.type,h.sceneid,h.qtype,h.remark,(case when h.interrupt=0 then 0 ELSE '' end),h.voxfile,h.questionnairetype,h.matchidlist from zt_know_hoodlemobileid h where h.HoodleMobileid=" + str(
        HoodleMobile) + " "
    HoodleMobileadress = conn.mysql_sel(sql, dbname, cid)
    print("******************************selHoodleMobileadressinterrupt**********************")
    print(sql)
    print(dbname, cid)
    # print(sql)
    print("***********************************************************************************")
    return HoodleMobileadress

@time_statistic(5,'cid')
def selcommon(cmpid, serviceid, num, name, dbname, cid):
    sqlcount = " select count(*) from zt_cmp_robot_vtype v ,zt_cmp_robot_global_answer a where v.typeid=a.typeid" \
               " and a.cmpid=" + str(cmpid) + " and a.serviceid=" + str(serviceid) + " and v.`name`='" + str(
                   name) + "'  " + " "

    allcount = conn.mysql_selrowid(sqlcount, dbname, cid)

    count = 0
    HoodleMobileadress = []
    if str(allcount) != '0':
        count = int(int(num) % int(allcount))

        if count != 0:
            count = count - 1
        else:
            count = allcount - 1

        sql = "SELECT a.content,a.ctype,a.text,a.type,a.intentionid,a.isend,a.voxfile from zt_cmp_robot_vtype v ,zt_cmp_robot_global_answer a " \
              " where v.typeid=a.typeid and a.cmpid=" + str(cmpid) + " and a.serviceid=" + str(
                  serviceid) + " and v.`name`='" + str(name) + "' " \
            ' ORDER BY a.degree  LIMIT ' + \
            str(count) + ',1 '

        HoodleMobileadress = conn.mysql_sel(sql, dbname, cid)

    return HoodleMobileadress


def selLastask(cid, serviceid, dbname):
    sql = "select a.HoodleMoboleid from zt_know_ask a,zt_know_hoodlemobileid h where a.cid='" + str(
        cid) + "' and  h.serviceid='" + str(serviceid) + "' and " \
                                                         " h.HoodleMobileid=a.HoodleMoboleid and h.sceneid   in (1,2)  ORDER BY a.createtime DESC LIMIT 0,1"
    ask = conn.mysql_selrowid(sql, dbname, cid)
    # print(sql)

    return ask


def backattitude(HoodleMobileid):
    # sql ="select GROUP_CONCAT(h.attitude,REPLACE (h.specialwords,',',CONCAT('|',h.attitude)) Separator '|' ) " \
    #      "  from zt_know_hoodlemobile_answer h where h.HoodleMobileid='"+str(HoodleMobileid)+"'"
    # attitude = conn.mysql_selrowid(sql)
    # if attitude==None:
    #     attitude=''
    attitude = ''
    return attitude


def exchangescene(cmpid, sceneid, serviceid, dbname, cid):
    sql = "select h.HoodleMobileadress ,h.HoodleMobileid,h.type,h.qtype,h.remark,(case when h.interrupt=0 then 0 ELSE '' end),h.voxfile,h.questionnairetype,h.matchidlist from " \
          "zt_know_hoodlemobileid h where h.cmpid='" + str(cmpid) + "' and h.sceneid='" + str(sceneid) + "' " \
                                                                                                         "and h.serviceid = '" + str(
              serviceid) + "' and h.state=1 ORDER BY h.seqencing asc  LIMIT 0,1  "
    HoodleMobileadress = conn.mysql_sel(sql, dbname, cid)
    # print(sql)
    return HoodleMobileadress


def verificationrobot(cmpid, serviceid, dbname, cid):
    sql = "select COUNT(*) from zt_cmp_robot r where r.cmpid='" + str(cmpid) + "' and r.onserviceid='" + str(
        serviceid) + "'"
    robotcount = conn.mysql_selrowid(sql, dbname, cid)
    return robotcount


# 更新机器人会话


def selsupergyrequestion(cid, dbname):
    # sqlhave = " select COUNT(*) from zt_know_ask a where a.cid='"+str(cid)+"' and a.senceid=8  "
    # havecount = conn.mysql_selrowid(sqlhave)
    # if havecount>=2:
    #     return ""
    sql = "select h.HoodleMobileadress,h.HoodleMobileid,h.type,h.qtype from zt_know_hoodlemobileid h ,zt_know_ask a  where a.HoodleMoboleid=h.HoodleMobileid " \
          "and a.cid='" + str(cid) + \
        "' and a.state=0  GROUP BY askid DESC LIMIT 0,1; "
    attach = conn.mysql_sel(sql, dbname, cid)
    # 准备改动4
    return attach


# 查询无配置答案的下一个问题
def notanswernethoodlemobile(cmpid, serviceid, askid, cid, dbname):
    # sql ="select h.HoodleMobileadress ,hINSERT into zt_know_ask(cmpid,HoodleMo.HoodleMobileid,h.type from zt_know_hoodlemobileid h" \
    #       " where h.cmpid='"+str(cmpid)+"' and h.serviceid='"+str(serviceid)+"' and h.state=1  " \
    #       " and h.seqencing>(SELECT  hh.seqencing  from zt_know_hoodlemobileid hh where hh.HoodleMobileid='"+str(askid)+"' )" \
    #       "  ORDER BY h.seqencing LIMIT 0,1"
    sql = "  select  h.HoodleMobileadress ,h.HoodleMobileid,h.type,h.sceneid,h.qtype,h.remark,(case when h.interrupt=0 then 0 ELSE '' end),h.voxfile,h.questionnairetype,h.matchidlist from zt_know_hoodlemobileid h " \
          "    where h.cmpid='" + str(cmpid) + "' and h.serviceid='" + str(
              serviceid) + "' and h.state=1  and h.sceneid in (1,2,3,4) and  " \
        " ( case when h.sceneid=(SELECT hh.sceneid from zt_know_hoodlemobileid hh  where hh.HoodleMobileid='" + str(
              askid) + "')" \
        " then h.seqencing>(SELECT hh.seqencing from zt_know_hoodlemobileid hh  where hh.HoodleMobileid='" + str(
              askid) + "') " \
        " and h.HoodleMobileid not in (select a.HoodleMoboleid from zt_know_ask a,zt_know_hoodlemobileid h where a.cid='" + str(
              cid) + "'" \
        "and  h.serviceid='" + str(serviceid) + "' and  h.HoodleMobileid=a.HoodleMoboleid)" \
        " else h.sceneid>(SELECT hh.sceneid from zt_know_hoodlemobileid hh  where hh.HoodleMobileid='" + str(
              askid) + "'" \
        " and h.HoodleMobileid not in (select a.HoodleMoboleid from zt_know_ask a,zt_know_hoodlemobileid h where a.cid='" + str(
              cid) + "'" \
        "and  h.serviceid='" + str(serviceid) + "' and  h.HoodleMobileid=a.HoodleMoboleid))" \
        " and h.HoodleMobileid not in (select a.HoodleMoboleid from zt_know_ask a,zt_know_hoodlemobileid h where a.cid='" + str(
              cid) + "'" \
        " and  h.serviceid='" + str(serviceid) + "' and  h.HoodleMobileid=a.HoodleMoboleid) end )" \
        "  and h.HoodleMobileid!='" + str(
              askid) + "'  ORDER BY h.sceneid,h.seqencing;"

    HoodleMobile = conn.mysql_sel(sql, dbname, cid)
    return HoodleMobile


# 查询无配置答案的下一个问题(不包括结束语)
def notanswernethoodlemobilenotend(cmpid, serviceid, askid, dbname, cid):
    # sql ="select h.HoodleMobileadress ,h.HoodleMobileid,h.type from zt_know_hoodlemobileid h" \
    #       " where h.cmpid='"+str(cmpid)+"' and h.serviceid='"+str(serviceid)+"' and h.state=1  " \
    #       " and h.seqencing>(SELECT  hh.seqencing  from zt_know_hoodlemobileid hh where hh.HoodleMobileid='"+str(askid)+"' )" \
    #       "  ORDER BY h.seqencing LIMIT 0,1"
    sql = "  select  h.HoodleMobileadress ,h.HoodleMobileid,h.type,h.sceneid,h.qtype,h.remark,(case when h.interrupt=0 then 0 ELSE '' end) from zt_know_hoodlemobileid h " \
          "    where h.cmpid='" + str(cmpid) + "' and h.serviceid='" + str(
              serviceid) + "' and h.state=1  and h.sceneid in (1,2,3) and  " \
        " ( case when h.sceneid=(SELECT hh.sceneid from zt_know_hoodlemobileid hh  where hh.HoodleMobileid='" + str(
              askid) + "')" \
        " then h.seqencing>(SELECT hh.seqencing from zt_know_hoodlemobileid hh  where hh.HoodleMobileid='" + str(
              askid) + "')  " \
        " else h.sceneid>(SELECT hh.sceneid from zt_know_hoodlemobileid hh  where hh.HoodleMobileid='" + str(
              askid) + "') end )" \
        "  and h.HoodleMobileid!='" + \
        str(askid) + "'  ORDER BY h.sceneid,h.seqencing;"

    HoodleMobile = conn.mysql_sel(sql, dbname, cid)

    return HoodleMobile


# 查询后面的语境
def sellastanswerscen(cid, cmpid, serviceid, askid, dbname):
    tu = notanswernethoodlemobile(cmpid, serviceid, askid, cid, dbname)
    if tu != None and len(tu) != 0:
        return tu
    else:
        sql = "SELECT a.HoodleMoboleid from zt_know_ask a where a.cid='" + str(
            cid) + "' and a.senceid in (1,2,3,4) ORDER BY a.senceid desc LIMIT 0,1 "
        HoodleMoboleid = conn.mysql_selrowid(sql, dbname, cid)

        if HoodleMoboleid != None and HoodleMoboleid != '':
            return notanswernethoodlemobile(cmpid, serviceid, HoodleMoboleid, cid, dbname)
        else:
            return selHoodleMobileadressinterrupt(HoodleMoboleid, dbname, cid)


# 查询后面的语境（子机器人特殊跳转，不能跳转到结束语）
def sellastanswerscennotend(cid, cmpid, serviceid, askid, dbname):
    tu = notanswernethoodlemobilenotend(cmpid, serviceid, askid, dbname, cid)
    if tu != None and len(tu) != 0:
        return tu
    else:
        sql = "SELECT a.HoodleMoboleid from zt_know_ask a where a.cid='" + str(
            cid) + "' and a.senceid in (1,2,3) ORDER BY a.senceid desc LIMIT 0,1 "
        HoodleMoboleid = conn.mysql_selrowid(sql, dbname, cid)

        if HoodleMoboleid != None and HoodleMoboleid != '':
            return notanswernethoodlemobilenotend(cmpid, serviceid, HoodleMoboleid, dbname, cid)
        else:
            return ''


def selanswertypeformongodb(dbname, cid):
    sql = "select a.answer ,a.answertypeid from zt_know_answerid a where a.state=1  "
    answertype = conn.mysql_sel(sql, dbname, cid)
    return answertype


def insertsessionsence(cid, scenceid, session_data, session_sort, dbname):
    sql = " insert into zt_cmp_robot_sessionscene (cid,sceneid,jointime,session_data,session_sort)" \
          " VALUES ('" + str(cid) + "','" + str(scenceid) + "',now(),'" + str(session_data) + "','" + str(
              session_sort) + "');"
    insercount = conn.mysql_insert(sql, dbname, cid)
    # print(sql)
    return insercount


# 久不应答后的，客户应答后
def getunanswerquestion(cmpid, cid, dbname):
    sql = "select * from (" \
          " select '1',a.HoodleMoboleid,a.createtime from zt_know_ask a where a.cmpid='" + str(
              cmpid) + "' and a.cid='" + str(cid) + "' and a.senceid not in (6,7)" \
        " union all" \
        " select '2' as num ,questionid,createtime from zt_cmp_robot_respond r where r.cid='" + str(
              cid) + "' and r.cmpid='" + str(cmpid) + "'" \
        " ) a ORDER BY a.createtime desc LIMIT 0,1;"
    answer = conn.mysql_sel(sql, dbname, cid)
    return answer


# 查询变量内容
def variantdata(variant, cid, dbname):
    sql = "select"
    if variant != 'level':
        if variant == 'customername' or variant == 'mobilephone':
            sql += " t." + variant + " "
        else:
            sql += " " + variant + " "
    else:
        sql += " (case WHEN " + variant + "=1 then '普通客户' else 'vip客户' end) "
    sql += "from zt_sale_data d ,zt_customer_tm t where t.tmcustid=d.temporarycustomerid and d.saleid='" + str(
        cid) + "' ;"
    data = conn.mysql_selrowid(sql, dbname, cid)

    return data


# 查询name变量内容

def variantdata1(userid, select_value, dbname, cid):
    sql = "select zg." + select_value + " from zt_guser zg,zt_guser_interest zgi WHERE zgi.userid = zg.UserID and zgi.userid='" + str(
        userid) + "' ;"
    name = conn.mysql_selrowid(sql, dbname, cid)
    return name


# 查询一套话术公司信息
def variant_cmp_info(cmpid, itemid, dbname, cid):
    sql = "SELECT count(*),c.content from zt_know_cmp_diy_item_content c where c.itemid='" + str(
        itemid) + "' and c.cmpid='" + str(cmpid) + "' and c.state=1"
    content = conn.mysql_sel(sql, dbname, cid)
    return content


# 查询一套话术默认公司信息
def variant_cmp_info_default(cmpid, itemid, dbname, cid):
    sql = "SELECT i.defvalue from zt_know_cmp_diy_item i where i.itemid='" + \
        str(itemid) + "'"
    content = conn.mysql_selrowid(sql, dbname, cid)
    return content


# 查询t.tmcustid  通过销售数据表的id查到 t.tmcustid
def seltmcustid(cid, dbname):
    sql = " select t.tmcustid from zt_sale_data d ,zt_customer_tm t where t.tmcustid=d.temporarycustomerid and d.saleid='" + str(
        cid) + "'"
    tmcustid = conn.mysql_selrowid(sql, dbname, cid)
    return tmcustid


def insert_zt_customer_attr_link(tmcustid, cid, session_data, session_sort, attrid, dbname):
    sql = "INSERT into zt_customer_attr_link(tmcustid,attrid,session_data,session_sort,saleid,type) " \
          " VALUES('" + str(tmcustid) + "','" + str(attrid) + "','" + str(session_data) + "','" + str(
              session_sort) + "','" + str(cid) + "',1)"

    conn.mysql_insertid(sql, dbname, cid)


def insert_zt_customer_attr_link_match(tmcustid, cid, askid, session_data, session_sort, matchid, dbname):
    sql = "INSERT into zt_customer_attr_link(tmcustid,matchid,session_data,session_sort,saleid,type,askid) " \
          " VALUES('" + str(tmcustid) + "','" + str(matchid) + "','" + str(session_data) + "','" + str(
              session_sort) + "','" + str(cid) + "',2,'" + str(askid) + "')"
    print(sql)
    conn.mysql_insertid(sql, dbname, cid)


# 查询自定义变量
def odervariantdata(variant, cid, cmpid, dbname):
    print(variant)
    if variant != "":
        if variant[0] == "{":
            variant = variant[1:]
    sqlid = "select i.itemid from zt_customer_diy_item i where i.itemname='" + str(variant) + "' and i.cmpid='" + str(
        cmpid) + "' and i.state=1 LIMIT 0,1"
    id = conn.mysql_selrowid(sqlid, dbname, cid)
    sql = " select (case when zt_customer_diy_item.itemtype not in (2,9,10) THEN  zt_customer_tm_diy_item_content.content" \
          " else (select group_concat(zt_customer_diy_item.itemname) from zt_customer_diy_item where " \
          " concat(',',zt_customer_tm_diy_item_content.content,',') like concat('%,',zt_customer_diy_item.itemid,',%') ) " \
          " END) a from zt_customer_tm_diy_item_content" \
          " LEFT JOIN zt_customer_diy_item on zt_customer_diy_item.itemid=zt_customer_tm_diy_item_content.itemid" \
          " where zt_customer_tm_diy_item_content.tmcustomerid=(select d.temporarycustomerid from zt_sale_data d where d.saleid='" + str(
              cid) + "') " \
        "  and zt_customer_diy_item.itemid='" + str(id) + "'"

    data = conn.mysql_selrowid(sql, dbname, cid)
    return data


# 查询转接语
def seltransforanwer(cmpid, serviceid, dbname, cid):
    sql = "select r.transforanswer,r.vtype,r.transforanswertext from zt_cmp_robot r where r.cmpid='" + str(
        cmpid) + "' and r.onserviceid='" + str(serviceid) + "' and r.state=1 "
    transforanswer = conn.mysql_sel(sql, dbname, cid)
    return transforanswer


# 查询短信语
def selnoteanswerback(cmpid, serviceid, dbname, cid):
    sql = "select r.noteanswer,r.vtype,r.noteanswertext from zt_cmp_robot r where r.cmpid='" + str(
        cmpid) + "' and r.onserviceid='" + str(serviceid) + "' and r.state=1 "
    noteanswer = conn.mysql_sel(sql, dbname, cid)
    return noteanswer


def selaskcount(cid, quesid, session_data, dbname):
    sql = "SELECT count(*) from ( select s.respondid from zt_cmp_robot_respond s where " \
          "  s.cid='" + str(cid) + "' and s.questionid='" + str(quesid) + "'  "
    if session_data != "":
        sql += " and s.session_data!='" + \
            str(session_data) + "' GROUP BY s.session_data"
    sql += ") a"
    count = conn.mysql_selrowid(sql, dbname, cid)
    if count == None or count == "":
        count = 0
    return count


# 查询问题编码
def selquesid(answerid, dbname, cid):
    sql = "select a.quesid from zt_know_answer a where a.answerid='" + \
        str(answerid) + "' "
    quesid = conn.mysql_selrowid(sql, dbname, cid)
    if quesid == None or quesid == '':
        sql = "select q.quesid from zt_know_question q where q.answerid='" + \
            str(answerid) + "'"
        quesid = conn.mysql_selrowid(sql, dbname, cid)
    return quesid


def selattrilist(quesid, dbname, cid):
    sql = "SELECT q.attrids from zt_know_question q where q.quesid=" + \
        str(quesid) + " "
    print('phonesql中的sql值', sql)
    attrids = conn.mysql_selrowid(sql, dbname, cid)
    print('attrids', attrids)
    return attrids


# 查询最后一句话是否是问题库
def selfinallyanswer(cid, session_data, dbname):
    sql = " select IFNULL(a.answerid,'0') from zy_rcustomerquestion c ,zy_robotanswer a where a.sessionid=c.sessionid and c.cid='" + str(
        cid) + "'"
    if session_data != "":
        sql += " and c.session_data!='" + str(session_data) + "' "
    sql += " ORDER BY a.Tdata DESC limit 0,1"
    answerid = conn.mysql_selrowid(sql, dbname, cid)

    if answerid == '':
        answerid = 0
    print('&&&&&&&&&&&&&&&&&&&&--answerid---&&&&&&&&&&&---phonesql', answerid)
    return answerid


# 查询是否需要转人工
def Judgmenttransfer(cid, cmpid, serviceid, dbname):
    # 查询当前分数
    print("转人工777777777777777777777777777777777777---phonesql")
    try:
        scoresql = " SELECT IFNULL(sum(ha.score),0)  from zt_know_hoodlemobile_answer ha ,zt_know_ask a " \
                   " where ha.HoodleMobileid=a.HoodleMoboleid and ha.attitude=a.answerid and a.cid='" + \
            str(cid) + "' "
        score = conn.mysql_selrowid(scoresql, dbname, cid)
        # print(scoresql)

        # 插入数据库分数
        # insertscoresql = " UPDATE zt_sale_data set score='"+str(score)+"'  where  saleid='"+str(cid)+"'"
        # insertscore = conn.mysql_insert(insertscoresql)

        # 查询是否要转人工
        sql = "select count(r.robotid),(case when r.transfertype=2 then r.transfernum  ELSE r.transferserviceid end),r.transfertype  from zt_cmp_robot r where r.transferscore<=" + str(
            score) + "  and r.cmpid=" + str(cmpid) + " " \
                                                     " and r.onserviceid=" + \
            str(serviceid) + " and r.istransfer=1"
        count = conn.mysql_sel(sql, dbname, cid)
        print(sql)
        print('phonesql中转人工的count', count)
        return count
    except:
        return (0, '')


def inserscore(cid, cmpid, serviceid):
    pass
    # scoresql = " SELECT IFNULL(sum(ha.score),0)  from zt_know_hoodlemobile_answer ha ,zt_know_ask a " \
    #           " where ha.HoodleMobileid=a.HoodleMoboleid and ha.attitude=a.answerid and a.cid='"+str(cid)+"' "
    # score = conn.mysql_selrowid(scoresql)
    # #print(score)
    # if score!='' and str(score)!='0':
    #     print('ddd')
    #     insertscoresql = " UPDATE zt_sale_data set score='"+str(score)+"'  where  saleid='"+str(cid)+"'"
    #     insertscore = conn.mysql_insert(insertscoresql)


# 更新转人工成功
def updatatransfer(cid):
    pass
    # sql = "UPDATE zt_sale_data set istransfer =1 where saleid='"+str(cid)+"'"
    # updata = conn.mysql_updata(sql)
    # return updata


# 更新挂断原因
def updatacloseresult(cid, result, dbname):
    # 0无人接听 1号码错误 2占线 3接听后挂断 4客户忙碌中稍后联系 5回答超时挂机 6多次拒绝挂机  7邀约拒绝  8脏话 9已转人工 10邀约成功
    sql = "UPDATE zt_sale_data set closeresult ='" + str(result) + "' "
    if str(result) == '10':
        sql += " ,isagree =1"
    sql += " where saleid='" + \
        str(cid) + "'  and (closeresult not in (7,10) or closeresult is null) "
    updata = conn.mysql_updata(sql, dbname, cid)

    # return updata


# 查询企业设置的拒接次数
def selrejectcount(cmpid, serviceid, dbname, cid):
    sql = "select (case when r.denialtimes is null then 2 else r.denialtimes end)" \
          " from zt_cmp_robot r where r.cmpid='" + \
        str(cmpid) + "' and r.onserviceid='" + str(serviceid) + "' "
    count = conn.mysql_selrowid(sql, dbname, cid)
    return count


# 技能查询
def selskill(skillid, wordslotid, dbname, cid):
    sql = "SELECT l.wordslotid,l.content,l.type,l.collecttype,l.remark from zt_skill s,zt_skill_wordslot l " \
          " where    l.skillid=s.skillid "
    if skillid != "":
        sql += " and s.skillid='" + str(skillid) + "' "
    if wordslotid != '':
        sql += " and l.sort>(SELECT ll.sort from zt_skill_wordslot ll where ll.wordslotid='" + \
            str(wordslotid) + "')"
    sql += " ORDER BY l.sort"
    sk = conn.mysql_sel(sql, dbname, cid)
    return sk


# 判断技能类型
def judgeskilltyoe(wordslotid, dbname, cid):
    sql = " select s.stype,s.skillid from zt_skill s,zt_skill_wordslot l where  s.skillid=l.skillid  and l.wordslotid='" + str(
        wordslotid) + "'"
    skilltype = conn.mysql_sel(sql, dbname, cid)
    return skilltype


# 结束采集数据
def endskillgather(skillid, dbname, cid):
    sql = " select s.endwords,s.type,s.remark,s.qtype from zt_skill s where s.skillid='" + \
        str(skillid) + "'"
    skilldata = conn.mysql_sel(sql, dbname, cid)
    return skilldata


# 插入要采集记录
def insertcollection(cid, wordslotid, dbname):
    sql = "INSERT into zt_skill_collection(cid,wordslotid,state) VALUES ('" + str(cid) + "','" + str(
        wordslotid) + "',1)"
    count = conn.mysql_insert(sql, dbname, cid)
    return count


# 拼接接口
def jointurl(skillid, cid, dbname):
    sql = " select CONCAT(s.url,'?') from zt_skill  s where  s.skillid='" + \
        str(skillid) + "' "
    url = conn.mysql_selrowid(sql, dbname, cid)
    urllinksql = " SELECT GROUP_CONCAT(CONCAT(l.urlparam,'=',c.text) SEPARATOR '&')  " \
                 " from zt_skill_collection c ,zt_skill_wordslot l where c.wordslotid=l.wordslotid " \
                 " and l.skillid='" + \
        str(skillid) + "' and c.cid='" + str(cid) + \
        "' and c.state=2  ORDER BY l.sort;"
    urllink = conn.mysql_selrowid(urllinksql, dbname, cid)
    if urllink != None:
        return url + urllink
    return url


# 存储采集到的数据
def storagecollection(wordslotid, cid, text, dbname):
    sql = "UPDATE zt_skill_collection set state=3 where wordslotid='" + str(wordslotid) + "'  and cid='" + str(
        cid) + "' and state=2; "
    upcount = conn.mysql_updata(sql, dbname, cid)
    sql = " UPDATE zt_skill_collection set state=2,text='" + str(text) + "' where wordslotid='" + str(
        wordslotid) + "'  and cid='" + str(cid) + "' ORDER BY collectionid desc LIMIT 1"
    upcount = conn.mysql_updata(sql, dbname, cid)
    return upcount


# 查看机器人是否已删除
def selifinuse(serviceid, dbname, cid):
    sql = "SELECT count(*) from zt_cmp_robot r where r.onserviceid='" + \
        str(serviceid) + "' and r.state=1"
    count = conn.mysql_selrowid(sql, dbname, cid)
    return count


# 查询主动提问编码是否还在
def selifinusehoodleMobile(hoodleMobileid, dbname, cid):
    sql = "SELECT h.state from zt_know_hoodlemobileid h,zt_cmp_robot r  where h.HoodleMobileid='" + str(
        hoodleMobileid) + "' and r.onserviceid=h.serviceid and r.state=1"
    count = conn.mysql_selrowid(sql, dbname, cid)
    return count


# 查询主动提问编码
def seldata(dbname, cid):
    sql = 'SELECT h.cmpid,h.HoodleMobileid from zt_know_hoodlemobileid h,zt_cmp_robot r  where h.state=1 and r.cmpid=h.cmpid and r.onserviceid=h.serviceid and h.sceneid!=4 and   h.cmpid=946'
    data = conn.mysql_sel(sql, dbname, cid)
    return data


def haverobotincmpservice(cmpid, dbname, cid):
    sql = "select r.cmpid,r.onserviceid from zt_cmp_robot r where r.state=1 and r.onserviceid!=0 and r.onserviceid is not  null and r.onserviceid!='' and r.cmpid='" + str(
        cmpid) + "'   "
    # sql = "select r.cmpid,r.onserviceid from zt_cmp_robot r where r.state=1 and (r.onserviceid=0 or r.onserviceid is  null or r.onserviceid='')   "
    rb = conn.mysql_sel(sql, dbname, cid)
    return rb


def haverobotincmp(dbname, cid):  # where r.cmpid not in (575,746,289,764 ) and r.cmpid>899
    sql = "select r.cmpid,r.onserviceid from zt_cmp_robot r   GROUP BY r.cmpid"
    rb = conn.mysql_sel(sql, dbname, cid)
    return rb


# 查询行业库
def selindu(industryid, dbname, cid):
    sql = "select q.question,CONCAT('A',q.answerid),q.quesid from zt_know_question q where q.industryid='" + str(
        industryid) + "' and q.state=1"
    data = conn.mysql_sel(sql, dbname, cid)
    return data


# 查询问题库
def selinquestion(cmpid, serviceid, dbname, cid):
    # sql = "select q.question,CONCAT('A',q.answerid),q.quesid from zt_know_question q where q.cmpid='"+str(cmpid)+"' and q.serviceid='"+str(serviceid)+"' and q.state=1"
    sql = "select q.question,CONCAT('A',q.answerid),q.quesid from zt_know_question q where q.cmpid='" + str(
        cmpid) + "' and q.serviceid is null and q.state=1"
    data = conn.mysql_sel(sql, dbname, cid)
    return data


# 查询服务问题库
def selinservicequestion(cmpid, serviceid, dbname, cid):
    sql = "select q.question,CONCAT('A',a.answerid),q.quesid from zt_know_question q ,zt_know_answer a   where  q.quesid=a.quesid and q.cmpid='" + str(
        cmpid) + "' and q.serviceid='" + str(serviceid) + "' and q.state=1 and (a.sort is NULL or a.sort=1)"
    # sql = "select q.question,CONCAT('A',q.answerid),q.quesid from zt_know_question q where q.cmpid='"+str(cmpid)+"' and q.serviceid is null and q.state=1"
    data = conn.mysql_sel(sql, dbname, cid)
    return data


def selasmequestion(questid, dbname, cid):
    sql = "SELECT s.question from zt_know_samequestion s where s.quesid='" + \
        str(questid) + "';"
    data = conn.mysql_sel(sql, dbname, cid)
    return data


def selinHoodleMobileid(cmpid, serviceid, dbname, cid):
    sql = "select h.HoodleMobileid from zt_know_hoodlemobileid h where h.cmpid='" + str(
        cmpid) + "' and h.serviceid='" + str(serviceid) + "' and h.state=1"
    # sql = "select h.HoodleMobileid from zt_know_hoodlemobileid h where h.cmpid='"+str(cmpid)+"' and q.serviceid is null and h.state=1"
    data = conn.mysql_sel(sql, dbname, cid)
    return data


def selinatt(HoodleMobileid, dbname, cid):
    sql = "select a.attitude,a.specialwords from zt_know_hoodlemobile_answer a where a.HoodleMobileid='" + str(
        HoodleMobileid) + "';"
    data = conn.mysql_sel(sql, dbname, cid)
    return data


# 查询行业
def getzt_industry(industryid, dbname, cid):
    sql = "select i.appid,i.apikey,i.authid from zt_industry i where i.industryid='" + \
        str(industryid) + "'  "
    data = conn.mysql_sel(sql, dbname, cid)
    return data


# 查询存储的格式
def selstoragejsonitem(wordslotid, key, item, cid, cmpid, dbname):
    try:
        data = ''
        insertname = ''
        # 查询技能编码
        sql = "select w.skillid from zt_skill s  ,zt_skill_wordslot w where w.skillid=s.skillid and w.wordslotid='" + str(
            wordslotid) + "';"
        skill = conn.mysql_selrowid(sql, dbname, cid)

        # 查询插入的字段
        selitemname = "select j.itemname from zt_skill_jsoninput J WHERE j.skillid='" + str(
            skill) + "' and j.jsonname='" + str(key) + "' and (j.type = '1' or j.type is null) "
        itenname = conn.mysql_selrowid(selitemname, dbname, cid)

        table = itenname.split('#')[1]  # {姓名#1#customername}
        if str(table) == '1':
            insertname = itenname.split('#')[-1][:-1]

        elif str(table) == '2':
            insertname = itenname.split('#')[-1][:-1]
        # 判断是否是呼入
        jugeifin = "select d.temporarycustomerid from zt_sale_data d where d.saleid='" + \
            str(cid) + "' ;"
        temporarycustomerid = conn.mysql_selrowid(jugeifin, dbname, cid)

        if temporarycustomerid == None or temporarycustomerid == '':
            insertem = "insert into zt_customer_tm (cmpid) VALUES ('" + \
                str(cmpid) + "');"
            temporarycustomerid = conn.mysql_insertid(insertem, dbname, cid)
            # 更新salaid中的temporarycustomerid
            updata = "UPDATE  zt_sale_data set temporarycustomerid='" + str(
                temporarycustomerid) + "' where saleid='" + str(cid) + "'"
            up = conn.mysql_updata(updata, dbname, cid)

        # 插入数据
        if str(table) == '1':
            updatadata = "UPDATE zt_customer_tm set " + insertname + "='" + str(item) + "' where tmcustid='" + str(
                temporarycustomerid) + "';"
            data = conn.mysql_updata(updatadata, dbname, cid)
        if str(table) == '2':
            exist_sql = """
            select
                count(*)
            from
                zt_customer_tm_diy_item_content
            where
                tmcustomerid = '{}'
                and itemid = '{}'
                and state = '1'
            """.format(
                temporarycustomerid, insertname)
            exist_record = conn.mysql_selrowid(exist_sql, dbname, cid)
            if exist_record > 0:
                wait_exec_sql = """
                update
                    zt_customer_tm_diy_item_content
                set
                    content = '{}'
                where
                    tmcustomerid = '{}'
                    and itemid = '{}'
                    and state = '1'
                """.format(item, temporarycustomerid, insertname)
            else:
                wait_exec_sql = "insert into zt_customer_tm_diy_item_content(tmcustomerid,itemid,content,state) VALUES ('" + str(
                    temporarycustomerid) + "','" + str(insertname) + "','" + str(item) + "',1);"
            data = conn.mysql_updata(wait_exec_sql, dbname, cid)
            # updatadata = "insert into zt_customer_tm_diy_item_content(tmcustomerid,itemid,content,state) VALUES ('"+str(temporarycustomerid)+"','"+str(insertname)+"','"+str(item)+"',1);"
            # data = conn.mysql_updata(updatadata,dbname,cid)
        return data
    except Exception as e:
        return ''


def updata_sex(tmcustid, dbname, cid):
    sql = "update zt_customer_tm set  sex=1 where tmcustid='" + \
        str(tmcustid) + "'"
    updatecount = conn.mysql_updata(sql, dbname, cid)


# 查询存储的格式
def selstoragejsonitemintnetion(intnentionid, key, item, cid, cmpid, dbname):
    try:
        data = ''
        insertname = ''
        # 查询技能编码
        # 查询插入的字段
        selitemname = "select j.itemname from zt_skill_jsoninput J WHERE j.skillid='" + str(
            intnentionid) + "' and j.jsonname='" + str(key) + "' and j.type='" + str(2) + "'"
        itenname = conn.mysql_selrowid(selitemname, dbname, cid)

        table = itenname.split('#')[1]  # {姓名#1#customername}
        if str(table) == '1':
            insertname = itenname.split('#')[-1][:-1]

        elif str(table) == '2':
            insertname = itenname.split('#')[-1][:-1]
        # 判断是否是呼入
        jugeifin = "select d.temporarycustomerid from zt_sale_data d where d.saleid='" + \
            str(cid) + "' ;"
        temporarycustomerid = conn.mysql_selrowid(jugeifin, dbname, cid)

        if temporarycustomerid == None or temporarycustomerid == '':
            insertem = "insert into zt_customer_tm (cmpid) VALUES ('" + \
                str(cmpid) + "');"
            temporarycustomerid = conn.mysql_insertid(insertem, dbname, cid)
            # 更新salaid中的temporarycustomerid
            updata = "UPDATE  zt_sale_data set temporarycustomerid='" + str(
                temporarycustomerid) + "' where saleid='" + str(cid) + "'"
            up = conn.mysql_updata(updata, dbname, cid)

        # 插入数据
        if str(table) == '1':
            updatadata = "UPDATE zt_customer_tm set " + insertname + "='" + str(item) + "' where tmcustid='" + str(
                temporarycustomerid) + "';"
            data = conn.mysql_updata(updatadata, dbname, cid)
        if str(table) == '2':
            updatadata = "insert into zt_customer_tm_diy_item_content(tmcustomerid,itemid,content,state) VALUES ('" + str(
                temporarycustomerid) + "','" + str(insertname) + "','" + str(item) + "',1);"
            data = conn.mysql_updata(updatadata, dbname, cid)
        return data
    except Exception as e:
        return ''


def get_dirty_sql(dbname, cid):
    sql = "SELECT d.dirty from zt_dirty d "
    dirtylist = conn.mysql_sel(sql, dbname, cid)
    return dirtylist


def insert_dirty_sql(dirty, dbname, cid):
    sql = " INSERT into zt_dirty(dirty) VALUES ('" + str(dirty) + "') "
    count = conn.mysql_insertid(sql, dbname, cid)
    return count


def del_dirty_sql(dirty, dbname, cid):
    sql = " DELETE from zt_dirty where dirty='" + str(dirty) + "' "
    count = conn.mysql_updata(sql, dbname, cid)
    return count


def sel_dirty_sql(dirty, pagesize, page, time, dbname, cid):
    sql = " SELECT dirty from zt_dirty where 1=1  "
    if dirty != "":
        sql += " and  dirty like '%" + str(dirty) + "%'"
    if time != "":
        sql += " and DATE_FORMAT(datetime,'%Y-%m-%d')=DATE_FORMAT(" + \
            str(time) + ",'%Y-%m-%d')"
    sql += " order by datetime desc LIMIT " + \
        str(int(pagesize) * (int(page) - 1)) + " , " + str(pagesize) + ""

    count = conn.mysql_seldic(sql, dbname, cid)
    return count


def sel_dirty_sql_count(dirty, time, dbname, cid):
    sql = " SELECT count(*) from zt_dirty where 1=1   "
    if dirty != "":
        sql += " and  dirty like '%" + str(dirty) + "%'"
    if time != "":
        sql += " and DATE_FORMAT(datetime,'%Y-%m-%d')=DATE_FORMAT(" + \
            str(time) + ",'%Y-%m-%d')"
    count = conn.mysql_selrowid(sql, dbname, cid)
    return count


def get_next_hoodlemobile_id(attitude_id, HoodleMobileAnswerid, db_name, cid):
    sql = '''
    select
        a.modelid,a.nextHoodleMobileid
    from
        zt_know_hoodlemobile_answer a
    where
        a.attitude ='{}'
        and a.HoodleMobileAnswerid ='{}'
    '''
    sql = sql.format(attitude_id, HoodleMobileAnswerid)
    data = conn.mysql_sel(sql, db_name, cid)
    if len(data) > 0 :
        return data[0][1] or data[0][0]
    return ''

def get_collect_btn_memo(hma_id, db_name, cid):
    """
    得到按键采集时的提示文本或语言
    :param hma_id: HoodleMobileAnswerid
    :param db_name: 数据库名
    :param cid: 会话编号
    :return:
    """
    sql = """select type, atype, content, remark from zt_know_hoodlemobile_answer where HoodleMobileAnswerid  = '{}' """
    sql = sql.format(hma_id)
    data = conn.mysql_sel(sql, db_name, cid)
    if len(data) == 0:
        return ()
    if not data[0]:
        return ()
    return data[0]